﻿namespace Pub.Class
{
    using System;
    using System.Collections.Generic;
    using System.Runtime.InteropServices;
    using System.Text;

    public class SQL
    {
        private StringBuilder deleteList = new StringBuilder();
        private bool distinct;
        private StringBuilder fromList = new StringBuilder();
        private StringBuilder groupList = new StringBuilder();
        private StringBuilder havingWhere = new StringBuilder();
        private IList<string> insertFields = new List<string>();
        private Dictionary<string, object> insertList = new Dictionary<string, object>();
        private string insertTable = string.Empty;
        private bool insertUseSelect = true;
        private bool isDelete;
        private string limit = string.Empty;
        private StringBuilder orderList = new StringBuilder();
        private StringBuilder selectList = new StringBuilder();
        private int top;
        private IList<string> updateKeyEqValue = new List<string>();
        private Dictionary<string, object> updateList = new Dictionary<string, object>();
        private string updateTable = string.Empty;
        private StringBuilder whereList = new StringBuilder();

        public SQL AndHaving(string left, string op, string right)
        {
            this.havingWhere.AppendFormat(" and {0} {1} {2} ".FormatWith(left, op, right), new object[0]);
            return this;
        }

        public SQL AndOn(string left, string op, string right)
        {
            this.fromList.AppendFormat(" and {0} {1} {2} ".FormatWith(left, op, right), new object[0]);
            return this;
        }

        public SQL AndWhere(string left, string op, string right)
        {
            this.whereList.AppendFormat(" and {0} {1} {2} ".FormatWith(left, op, right), new object[0]);
            return this;
        }

        public string As(string alias)
        {
            return "({0}) as {1}".FormatWith(this.ToString(), alias);
        }

        public SQL Count()
        {
            return this.Count("*");
        }

        public SQL Count(string field)
        {
            if (field.IsNullEmpty())
            {
                return this.Count("*");
            }
            this.selectList.Clear();
            this.selectList.AppendFormat("count({0})", field);
            return this;
        }

        public SQL Delete()
        {
            this.isDelete = true;
            return this;
        }

        public SQL Delete(params string[] tables)
        {
            foreach (string str in tables)
            {
                this.deleteList.AppendFormat("{0},", str);
            }
            this.isDelete = true;
            return this;
        }

        public SQL Distinct()
        {
            this.distinct = true;
            return this;
        }

        public SQL From(params string[] tables)
        {
            if (!tables.IsNull())
            {
                foreach (string str in tables)
                {
                    this.fromList.AppendFormat("{0},", str);
                }
            }
            return this;
        }

        public SQL FromAlias(string table, string alias = "")
        {
            if (!table.IsNull())
            {
                this.fromList.AppendFormat("{0}{1},", table, alias.IsNullEmpty() ? "" : (" as " + alias));
            }
            return this;
        }

        public SQL GroupBy(string field)
        {
            if (!field.IsNullEmpty())
            {
                this.groupList.AppendFormat("{0},", field);
            }
            return this;
        }

        public SQL GroupBy(params string[] fields)
        {
            if (!fields.IsNull())
            {
                foreach (string str in fields)
                {
                    this.groupList.AppendFormat("{0},", str);
                }
            }
            return this;
        }

        public SQL Having(Pub.Class.Where where)
        {
            if (!where.IsNull())
            {
                this.havingWhere.AppendFormat("{0} ", where.ToString().TrimStart(3).Trim());
            }
            return this;
        }

        public SQL Having(string where)
        {
            if (!where.IsNullEmpty())
            {
                this.havingWhere.AppendFormat("{0} ", where);
            }
            return this;
        }

        public SQL Having(string left, string op, string right)
        {
            this.havingWhere.AppendFormat(" {0} {1} {2} ".FormatWith(left, op, right), new object[0]);
            return this;
        }

        public SQL InnerJoin(string table, string alias = "")
        {
            if ((!this.fromList.ToString().IsNullEmpty() && !table.IsNullEmpty()) && (this.fromList.ToString().CharacterCount(',') <= 1))
            {
                this.fromList.RemoveLastChar(1);
                this.fromList.AppendFormat(" inner join {0}{1}", table, alias.IsNullEmpty() ? "" : (" as " + alias));
            }
            return this;
        }

        public SQL Insert(string table)
        {
            this.insertTable = table;
            return this;
        }

        public SQL Insert(string table, params string[] fields)
        {
            this.insertTable = table;
            foreach (string str in fields)
            {
                if (str.StartsWith(this.insertTable + "."))
                {
                    this.insertFields.Add(str.Substring(this.insertTable.Length + 1));
                }
                else
                {
                    this.insertFields.Add(str);
                }
            }
            return this;
        }

        public SQL LeftJoin(string table, string alias = "")
        {
            if ((!this.fromList.ToString().IsNullEmpty() && !table.IsNullEmpty()) && (this.fromList.ToString().CharacterCount(',') <= 1))
            {
                this.fromList.RemoveLastChar(0x2c);
                this.fromList.AppendFormat(" left join {0}{1}", table, alias.IsNullEmpty() ? "" : (" as " + alias));
            }
            return this;
        }

        public SQL Limit(int rows)
        {
            this.limit = rows.ToString();
            return this;
        }

        public SQL Limit(long offset, int rows)
        {
            this.limit = offset.ToString() + "," + rows.ToString();
            return this;
        }

        public SQL Max(string field)
        {
            if (!field.IsNullEmpty())
            {
                this.selectList.Clear();
                this.selectList.AppendFormat("max({0})", field);
            }
            return this;
        }

        public SQL Min(string field)
        {
            if (!field.IsNullEmpty())
            {
                this.selectList.Clear();
                this.selectList.AppendFormat("min({0})", field);
            }
            return this;
        }

        public SQL On(Pub.Class.Where where)
        {
            if (!where.IsNull())
            {
                this.fromList.AppendFormat("{0} ", where.ToSql());
            }
            return this;
        }

        public SQL On(string where = "", bool addon = false)
        {
            if (where.IsNullEmpty())
            {
                this.fromList.AppendFormat("{0}1=1 ".FormatWith(addon ? " on " : ""), new object[0]);
                return this;
            }
            this.fromList.AppendFormat("{1}{0} ", where, addon ? " on " : "");
            return this;
        }

        public SQL On(string left, string op, string right)
        {
            this.fromList.AppendFormat(" on {0} {1} {2} ".FormatWith(left, op, right), new object[0]);
            return this;
        }

        public SQL OrderBy(string field)
        {
            if (!field.IsNullEmpty())
            {
                this.orderList.AppendFormat("{0},", field);
            }
            return this;
        }

        public SQL OrderByDescending(string field)
        {
            if (!field.IsNullEmpty())
            {
                this.orderList.AppendFormat("{0} desc,", field);
            }
            return this;
        }

        public SQL OrHaving(string left, string op, string right)
        {
            this.havingWhere.AppendFormat(" or {0} {1} {2} ".FormatWith(left, op, right), new object[0]);
            return this;
        }

        public SQL OrOn(string left, string op, string right)
        {
            this.fromList.AppendFormat(" or {0} {1} {2} ".FormatWith(left, op, right), new object[0]);
            return this;
        }

        public SQL OrWhere(string left, string op, string right)
        {
            this.whereList.AppendFormat(" or {0} {1} {2} ".FormatWith(left, op, right), new object[0]);
            return this;
        }

        public SQL RightJoin(string table, string alias = "")
        {
            if ((!this.fromList.ToString().IsNullEmpty() && !table.IsNullEmpty()) && (this.fromList.ToString().CharacterCount(',') <= 1))
            {
                this.fromList.RemoveLastChar(0x2c);
                this.fromList.AppendFormat(" right join {0}{1}", table, alias.IsNullEmpty() ? "" : (" as " + alias));
            }
            return this;
        }

        public SQL Select()
        {
            return this.Select(new string[] { "*" });
        }

        public SQL Select(params string[] fields)
        {
            if (fields.IsNull())
            {
                return this.Select(new string[] { "*" });
            }
            foreach (string str in fields)
            {
                this.selectList.AppendFormat("{0},", str);
            }
            return this;
        }

        public SQL Set(string keyEqValue)
        {
            this.updateKeyEqValue.Add(keyEqValue);
            return this;
        }

        public SQL Set(string key, object value)
        {
            return this.Set(key, value, false);
        }

        public SQL Set(string key, object value, bool useNULL)
        {
            if (!value.IsNull() || useNULL)
            {
                if (key.StartsWith(this.updateTable + "."))
                {
                    key = key.Substring(this.updateTable.Length + 1);
                }
                if (!this.updateList.ContainsKey(key))
                {
                    this.updateList.Add(key, value);
                }
                else
                {
                    this.updateList[key] = value;
                }
            }
            return this;
        }

        public string ToDeleteString()
        {
            StringBuilder builder = new StringBuilder();
            if (this.isDelete)
            {
                builder.Append("delete ");
                if (this.deleteList.Length > 0)
                {
                    this.deleteList.RemoveLastChar(",");
                    if ((this.deleteList.ToString().CharacterCount(',') == 0) && (this.fromList.Length == 0))
                    {
                        builder.Append("from ");
                    }
                    builder.AppendFormat("{0} ", this.deleteList.ToString());
                }
                if (this.fromList.Length > 0)
                {
                    builder.AppendFormat("from {0} \n", this.fromList.ToString().TrimEnd(new char[] { ',' }));
                }
                else
                {
                    builder.Append("\n");
                }
                if (this.whereList.Length > 0)
                {
                    builder.AppendFormat("where {0} \n", this.whereList.ToString());
                }
            }
            return builder.ToString();
        }

        public string ToInsertString()
        {
            Action<string> action = null;
            Action<KeyValuePair<string, object>> action2 = null;
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSql2 = new StringBuilder();
            if (!this.insertTable.IsNullEmpty())
            {
                if (this.insertUseSelect && (this.insertFields.Count > 0))
                {
                    strSql.AppendFormat("insert into {0}(", this.insertTable);
                    if (action == null)
                    {
                        action = delegate (string p) {
                            strSql.AppendFormat("{0},", p);
                        };
                    }
                    this.insertFields.Do<string>(action);
                    strSql.RemoveLastChar(1);
                    strSql.AppendFormat(") \n{0}", this.ToSelectString());
                }
                else if (!this.insertUseSelect && (this.insertList.Count > 0))
                {
                    if (action2 == null)
                    {
                        action2 = delegate (KeyValuePair<string, object> p) {
                            strSql.AppendFormat("{0},", p.Value.IsNull() ? "null" : Pub.Class.Where.ValueToStr(p.Value));
                            strSql2.AppendFormat("{0},", p.Key);
                        };
                    }
                    this.insertList.Do<KeyValuePair<string, object>>(action2);
                    strSql2.Insert(0, "insert into {0}(".FormatWith(this.insertTable)).RemoveLastChar(1);
                    strSql2.Append(") \nvalues(");
                    strSql.Insert(0, strSql2.ToString()).RemoveLastChar(1);
                    strSql.Append(")");
                }
            }
            return strSql.ToString();
        }

        public SQL Top(int n)
        {
            this.top = n;
            return this;
        }

        public string ToSelectString()
        {
            StringBuilder builder = new StringBuilder();
            if (!this.selectList.ToString().IsNullEmpty())
            {
                builder.Append("select ");
                if (this.distinct)
                {
                    builder.Append("distinct ");
                }
                if (this.top > 0)
                {
                    builder.AppendFormat("top {0} ", this.top);
                }
                builder.AppendFormat("{0} \n", this.selectList.ToString().TrimEnd(new char[] { ',' }));
                if (this.fromList.Length > 0)
                {
                    builder.AppendFormat("from {0} \n", this.fromList.ToString().TrimEnd(new char[] { ',' }));
                }
                if (this.limit.Length > 0)
                {
                    builder.AppendFormat("limit {0} \n", this.limit);
                }
                if (this.whereList.Length > 0)
                {
                    builder.AppendFormat("where {0} \n", this.whereList.ToString());
                }
                if (this.groupList.Length > 0)
                {
                    builder.AppendFormat("group by {0} ", this.groupList.ToString().TrimEnd(new char[] { ',' }));
                    if (this.havingWhere.Length > 0)
                    {
                        builder.AppendFormat("having {0} \n", this.havingWhere.ToString());
                    }
                    else
                    {
                        builder.Append("\n");
                    }
                }
                if (this.orderList.Length > 0)
                {
                    builder.AppendFormat("order by {0} \n", this.orderList.ToString().TrimEnd(new char[] { ',' }));
                }
            }
            return builder.ToString();
        }

        public override string ToString()
        {
            if (!this.insertTable.IsNullEmpty())
            {
                return this.ToInsertString();
            }
            if (!this.updateTable.IsNullEmpty())
            {
                return this.ToUpdateString();
            }
            if (this.isDelete)
            {
                return this.ToDeleteString();
            }
            if (!this.selectList.ToString().IsNullEmpty())
            {
                return this.ToSelectString();
            }
            return string.Empty;
        }

        public string ToUpdateString()
        {
            Action<KeyValuePair<string, object>> action = null;
            Action<string> action2 = null;
            StringBuilder builder = new StringBuilder();
            StringBuilder strSql2 = new StringBuilder();
            if (!this.updateTable.IsNullEmpty())
            {
                if (action == null)
                {
                    action = delegate (KeyValuePair<string, object> p) {
                        strSql2.AppendFormat("{0}={1},", p.Key, p.Value.IsNull() ? "null" : Pub.Class.Where.ValueToStr(p.Value));
                    };
                }
                this.updateList.Do<KeyValuePair<string, object>>(action);
                if (action2 == null)
                {
                    action2 = delegate (string p) {
                        strSql2.AppendFormat("{0},", p);
                    };
                }
                this.updateKeyEqValue.Do<string>(action2);
                if (strSql2.Length == 0)
                {
                    return string.Empty;
                }
                strSql2.RemoveLastChar(",");
                builder.AppendFormat("update {0} set {1}", this.updateTable, strSql2.ToString());
                if (this.fromList.Length > 0)
                {
                    builder.AppendFormat("from {0} \n", this.fromList.ToString().TrimEnd(new char[] { ',' }));
                }
                else
                {
                    builder.Append("\n");
                }
                if (this.whereList.Length > 0)
                {
                    builder.AppendFormat("where {0} \n", this.whereList.ToString());
                }
            }
            return builder.ToString();
        }

        public SQL Update(string table)
        {
            this.updateTable = table;
            return this;
        }

        public SQL Value(string field, object value)
        {
            return this.Value(field, value, false);
        }

        public SQL Value(string field, object value, bool useNULL)
        {
            if (!value.IsNull() || useNULL)
            {
                this.insertUseSelect = false;
                if (field.StartsWith(this.insertTable + "."))
                {
                    field = field.Substring(this.insertTable.Length + 1);
                }
                if (!this.insertList.ContainsKey(field))
                {
                    this.insertList.Add(field, value);
                }
                else
                {
                    this.insertList[field] = value;
                }
            }
            return this;
        }

        public SQL Values(params object[] values)
        {
            this.insertUseSelect = false;
            if (this.insertFields.Count == values.Length)
            {
                int index = 0;
                foreach (string str in this.insertFields)
                {
                    if (!this.insertList.ContainsKey(str))
                    {
                        this.insertList.Add(str, values[index]);
                    }
                    else
                    {
                        this.insertList[str] = values[index];
                    }
                    index++;
                }
            }
            return this;
        }

        public SQL Where(Pub.Class.Where where)
        {
            if (!where.IsNull())
            {
                this.whereList.AppendFormat("{0} ", where.ToSql());
            }
            return this;
        }

        public SQL Where(string where = "")
        {
            if (!where.IsNullEmpty())
            {
                this.whereList.AppendFormat("{0} ", where.IsNullEmpty() ? "1=1" : where);
            }
            return this;
        }

        public SQL Where(string left, string op, string right)
        {
            this.whereList.AppendFormat(" {0} {1} {2} ".FormatWith(left, op, right), new object[0]);
            return this;
        }
    }
}

